# -*- coding: utf-8 -*-
# coding=utf-8
"""

@author: fuhaishan
"""

import os
import codecs

# from openpyxl.reader.excel import load_workbook
from openpyxl import load_workbook

"""
    解析文件的配置参数
"""
config = {
    'dbXlsxFileName': '/Users/user/Documents/GitHub/axb-api/doc/数据词典/用户.xlsx',
    'dbName': '',
    'sqlConfig': {
        'schema': 'postgres'
    }
}

"""
    解析excel文件,基于openpyxl 2.6.2
"""


def analysisile():
    # region 1、加载xlsx文件，解析sheet

    xlsx_file = config['dbXlsxFileName']
    wb = load_workbook(xlsx_file)
    sheet_names = wb.sheetnames
    # endregion 1、加载xlsx文件，解析sheet

    # 生成的sql语句
    sql_str = []
    sql_comment_str = []

    for sheet_name in sheet_names:
        sheet = wb[sheet_name]

        table_name = sheet['D4'].value
        sql_table_name = table_name

        table_desc = sheet['D5'].value

        if not sql_table_name:
            continue

        sql_str.append('''DROP TABLE IF EXISTS ''' + sql_table_name + ''';''')
        sql_str.append('CREATE TABLE ' + sql_table_name + ' (')

        # 生成字段(第一行字段默认为主键)
        max_row = sheet.max_row

        for sheet_row in range(0, max_row):

            if sheet_row < 8:
                continue

            column_name = sheet.cell(row=sheet_row, column=2).value
            if not column_name:
                break

            column_desc = sheet.cell(row=sheet_row, column=3).value
            if not column_desc:
                column_desc = ''

            value_set = sheet.cell(row=sheet_row, column=8).value
            if not value_set:
                value_set = ''
            value_set = value_set.replace('\r', '').replace('\n', '')

            data_type = sheet.cell(row=sheet_row, column=4).value

            default_value = sheet.cell(row=sheet_row, column=7).value
            if default_value:
                if data_type.find('varchar') >= 0 or data_type.find('timestamp') >= 0:
                    default_value = "'" + default_value + "'"

            # 构建每一个字段的sql
            row_sql_str = '`' + column_name + '` ' + data_type

            # 非空标识
            empty_flag = sheet.cell(row=sheet_row, column=5).value
            if empty_flag == "Y":
                row_sql_str = row_sql_str + ' NOT NULL'

            # 默认值
            default_value = getDefaultValue(data_type, default_value, empty_flag)
            if default_value:
                row_sql_str = row_sql_str + ' DEFAULT ' + default_value

            # 抽取注释
            comment = " COMMENT '" + column_desc + '；' + value_set + "'"

            row_sql_str = row_sql_str + comment

            sql_str.append("    " + row_sql_str + ",")

        primary_column_name = sheet.cell(row=8, column=2).value
        sql_str.append(' PRIMARY KEY (`' + primary_column_name + '`)')
        sql_str.append(") COMMENT='" + table_desc + "';")
        sql_str.append("")

    # 写入sql文件
    writer_file_path = os.path.join(os.getcwd(), 'm_table.sql')
    os.remove(writer_file_path)
    write_file = codecs.open(writer_file_path, 'a', "utf-8")
    write_file.writelines('\r\n'.join(sql_str))


def getDefaultValue(data_type, default_value, empty_flag):
    """
    
    """
    data_type = data_type.lower()
    if empty_flag:
        empty_flag = empty_flag.lower()

    if default_value:

        if data_type.startswith('varchar'):
            default_value = data_type

    else:

        if empty_flag == "y":
            return ''

        if data_type.lower().startswith('varchar'):
            default_value = "NULL"

        if data_type in ['int', 'bigint', 'long', 'float', 'double']:
            default_value = '0'

        if data_type.lower().startswith('decimal'):
            default_value = '0'

        if data_type in ['datetime', 'date', 'time', 'smalldatetime']:
            default_value = 'NOW()'

        if data_type.lower().startswith('bool'):
            default_value = 'false'

    return default_value


analysisile()
print('success')
